package com.yaya.common.util;

import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Constructor;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;

public class ExcelUtil {

    //私有化，禁止实例化
    private ExcelUtil(){}

    //读取Excel
    public static <T> List<T> readExcel(String filepath,Class<T> tClass) throws Exception {
        ArrayList<T> dataList = new ArrayList<>();
        ArrayList<String> errorData = new ArrayList<>(); // 存储失败的数据
        try (
            FileInputStream fis = new FileInputStream(filepath);
            XSSFWorkbook workbook = new XSSFWorkbook(fis)) {
            XSSFSheet sheetAt = workbook.getSheetAt(0);
            for (Row row : sheetAt) {
                if (row.getRowNum() == 0) continue; //跳过表头
                try {
                    Constructor<T> constructor = tClass.getConstructor(); //反射创建对象
                    T instance = constructor.newInstance();

                    //Excel中的顺序于类的属性顺序一致
                    Field[] fields = tClass.getDeclaredFields();
                    for (int i = 0; i < fields.length; i++) {
                        fields[i].setAccessible(true);
                        String value = row.getCell(i).getStringCellValue();
                        fields[i].set(instance, value);
                    }
                    dataList.add(instance);
                } catch (Exception e){
                    errorData.add(row.getRowNum() + "行数据导入失败"); // 记录失败行
                }

            }
        }
        // 如果有错误数据，写入新表
        if (!errorData.isEmpty()) {
            writeErrorData("errorData.xlsx", errorData);
        }

        return dataList;
    }

    // 将失败的数据写入新Excel表
    private static void writeErrorData(String filePath, List<String> errorData) throws IOException {
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("Error Data");

        // 写入错误信息
        for (int i = 0; i < errorData.size(); i++) {
            Row row = sheet.createRow(i);
            row.createCell(0).setCellValue(errorData.get(i));
        }

        try (FileOutputStream fos = new FileOutputStream(filePath)) {
            workbook.write(fos);
        }
        workbook.close();
    }


    // 将对象列表写入Excel文件
    public static <T> void writeExcel(String filePath, List<T> dataList) throws IOException, IllegalAccessException {
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("Sheet1");

        // 写入表头
        Row headerRow = sheet.createRow(0);
        Field[] fields = dataList.get(0).getClass().getDeclaredFields();
        for (int i = 0; i < fields.length; i++) {
            headerRow.createCell(i).setCellValue(fields[i].getName());
        }

        // 写入数据
        int rowNum = 1;
        for (T data : dataList) {
            Row row = sheet.createRow(rowNum++);
            for (int i = 0; i < fields.length; i++) {
                fields[i].setAccessible(true);
                row.createCell(i).setCellValue((String) fields[i].get(data)); // 获取属性值
            }
        }

        try (FileOutputStream fos = new FileOutputStream(filePath)) {
            workbook.write(fos);
        }
        workbook.close();
    }

}
